------------------------------------
--
-- Add the tracking columns for bidirectional 
-- and download only synchronization:
-- * Add a timestamp column to record the logical time that the row was last updated.
-- * Add a bigint column to record the logical time that the row was inserted.
--	 A bigint column is used because a table can have only one timestamp column.
-- * Add InsertId and UpdateId columns to identify where changes were made.
--   Specify a default of 0 to indicate a server update.

-- Customer
ALTER TABLE wcfSyncSamplesDb.Sales.Customer 
	ADD UpdateTimestamp timestamp
ALTER TABLE wcfSyncSamplesDb.Sales.Customer 
	ADD InsertTimestamp binary(8) DEFAULT @@DBTS + 1
ALTER TABLE wcfSyncSamplesDb.Sales.Customer 
	ADD UpdateId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
ALTER TABLE wcfSyncSamplesDb.Sales.Customer 
	ADD InsertId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
GO

-- We add indexes to the Customer and Customer_Tombstone tables
-- to emphasize that you should take indexes into account
-- when you implement change-tracking in the server database.
-- Balance server performance against synchronization performance.
CREATE NONCLUSTERED INDEX IX_Customer_UpdateTimestamp
ON Sales.Customer(UpdateTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_InsertTimestamp
ON Sales.Customer(InsertTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_UpdateId
ON Sales.Customer(UpdateId)

CREATE NONCLUSTERED INDEX IX_Customer_InsertId
ON Sales.Customer(InsertId)
GO

------------------------------------
--
-- Create tombstone tables to store deletes.
-- Each tombstone table includes all columns from the base table, except those
-- we added for tracking. All columns are required if you must have
-- access to the whole row for conflict resolution.
--
CREATE TABLE wcfSyncSamplesDb.Sales.Customer_Tombstone(
	CustomerId uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED, 
	CustomerName nvarchar(100) NOT NULL,
	SalesPerson nvarchar(100) NOT NULL,
	CustomerType nvarchar(100) NOT NULL,
	DeleteId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
	DeleteTimestamp timestamp)
GO

CREATE CLUSTERED INDEX IX_Customer_Tombstone_DeleteTimestamp
ON Sales.Customer_Tombstone(DeleteTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_Tombstone_DeleteId
ON Sales.Customer_Tombstone(DeleteId)
GO

------------------------------------
-- Create delete triggers.
-- When a delete occurs in the base table, the trigger inserts a row 
-- in the tombstones table. Before performing an insert, the trigger 
-- checks whether the tombstones table already contains a row that has 
-- the primary key of a deleted row. This occurs if a row has been deleted 
-- from the base table, reinserted, and deleted again. If such a row is 
-- detected in the tombstones table, the trigger deletes the row and 
-- reinserts it.
--

CREATE TRIGGER Customer_DeleteTrigger 
ON wcfSyncSamplesDb.Sales.Customer FOR DELETE 
AS 
BEGIN 
    SET NOCOUNT ON
    DELETE FROM wcfSyncSamplesDb.Sales.Customer_Tombstone 
		WHERE CustomerId IN (SELECT CustomerId FROM deleted)
    INSERT INTO wcfSyncSamplesDb.Sales.Customer_Tombstone (CustomerId, CustomerName, SalesPerson, CustomerType) 
	SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM deleted
    SET NOCOUNT OFF
END
GO


--NOTE: Not TEST, yet. (July 26th 2009)
--Further information at http://msdn.microsoft.com/en-us/library/bb902828.aspx
CREATE PROCEDURE usp_GetNewBatchAnchor (
	@sync_last_received_anchor timestamp, 
	@sync_batch_size bigint,
	@sync_max_received_anchor timestamp out,
	@sync_new_received_anchor timestamp out,            
	@sync_batch_count int output)            
AS            
       -- Set a default batch size if a valid one is not passed in.
       IF  @sync_batch_size IS NULL OR @sync_batch_size <= 0
	     SET @sync_batch_size = 100    

	   -- Before selecting the first batch of changes,
	   -- set the maximum anchor value for this synchronization session.
       -- After the first time that this procedure is called, 
	   -- Synchronization Services passes a value for @sync_max_received_anchor
       -- to the procedure. Batches of changes are synchronized until this 
       -- value is reached.
       IF @sync_max_received_anchor IS NULL
         SELECT  @sync_max_received_anchor = MIN_ACTIVE_ROWVERSION() - 1
       
       -- If this is the first synchronization session for a database,
       -- get the lowest timestamp value from the tables. By default,
       -- Synchronization Services uses a value of 0 for @sync_last_received_anchor
       -- on the first synchronization. If you do not set @sync_last_received_anchor,
       -- this can cause empty batches to be downloaded until the lowest
       -- timestamp value is reached.
       IF @sync_last_received_anchor IS NULL OR @sync_last_received_anchor = 0
       BEGIN
                
		SELECT @sync_last_received_anchor = MIN(TimestampCol) FROM (
		  SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.Customer
		  UNION
		  SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.Customer
		) MinTimestamp	
       
		SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size

		-- Determine how many batches are required during the initial synchronization.
		IF @sync_batch_count <= 0
		  SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_last_received_anchor /  @sync_batch_size))

		END

       ELSE
       BEGIN

        SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size

        -- Determine how many batches are required during subsequent synchronizations.
		IF @sync_batch_count <= 0
          SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_new_received_anchor /  @sync_batch_size)) + 1  
       
	   END

       -- Check whether this is the last batch.      
       IF @sync_new_received_anchor >= @sync_max_received_anchor
       BEGIN

         SET @sync_new_received_anchor = @sync_max_received_anchor        
         IF @sync_batch_count <= 0
           SET @sync_batch_count = 1

       END
GO



